from shapely.geometry import Point
from sqlalchemy import *

p1 = '116.454,39.969'
p2 = '116.536,39.965'
start_time = '16:00'
city_id = '2'

one_travel_time = """
        select travel_time from travel_time
         where city_id='{0}' and p1::text='{1}' and p2::text='{2}' and travel_type='bus' and start={3} limit 1
    """

conn = create_engine('postgresql+psycopg2://hlj_lbs:tI8yYp7fZLCgqjT7@fe-pro-mdb.pg.rds.aliyuncs.com:3433/hlj_lbs',
# conn = create_engine('postgresql+psycopg2://postgres:postgres@172.16.254.164/postgres',
                         max_overflow=0,        # 超出连接池数量不增加线程
                         pool_size=20,          # 连接池大小
                         pool_timeout=30,       # 连接超时时间
                         pool_recycle=-1,       # 是否回收连接
                         pool_pre_ping=True     # 是否ping连接（断线重连）
                         )
p1_hex = Point(float(p1.split(',')[0]), float(p1.split(',')[1])).wkb_hex
p2_hex = Point(float(p2.split(',')[0]), float(p2.split(',')[1])).wkb_hex
start_hour = int(start_time.split(':')[0])
time_sql = one_travel_time.format(city_id, p1_hex, p2_hex, start_hour)
travel_time = None
print(time_sql)
with conn.connect() as con:
    rows = con.execute(time_sql)
    row = rows.fetchone()
    if row is not None:
        travel_time = row['travel_time']
print(travel_time)
